Create FOREIGN Table

A FOREIGN table is table that is defined on PHYSICAL model that represents a real relational table in source databases like Oracle, SQLServer etc. For relational databases, Teiid has capability to automatically retrieve the database schema information upon the deployment of the VDB, if one like to auto import the existing schema. However, user can use below FOREIGN table semantics, when they would like to explicitly define tables on PHYSICAL models or represent non-relational data as relational in custom translators.

CREATE FOREIGN TABLE Customer (id integer PRIMARY KEY, firstname varchar(25), lastname varchar(25),  dob timestamp);

CREATE FOREIGN TABLE Order (id integer PRIMARY KEY, customerid integer, saledate date, amount decimal(25,4) CONSTRAINT FOREGIN KEY(customerid) REFERENCES Customer(id));

Create View

A view is a virtual table. A view contains rows and columns,like a real table. The fields in a view are fields from one or more real tables from the source or other view models. They can also be expressions made up multiple columns, or aggregated columns. When column definitions are not defined on the view table, they will be derived from the projected columns of the view's select transformation that is defined after the AS keyword.

You can add functions, JOIN statements and WHERE clauses to a view data as if the data were coming from one single table.

TABLE/VIEW OPTIONS: (the below are well known options, any others properties defined will be considered as extension metadata)

CREATE VIEW CustomerOrders (name varchar(50),  saledate date, amount decimal) OPTIONS (CARDINALITY 100, ANNOTATION 'Example')
  AS
  SELECT concat(c.firstname, c.lastname) as name, o.saledate as saledate, o.amount as amount FROM Customer C JOIN Order o ON c.id = o.customerid;

COLUMN OPTIONS: (the below are well known options, any others properties defined will be considered as extension metadata)

CREATE VIEW CustomerOrders (name varchar(50),  saledate date, amount decimal CONSTRAINT
   INDEX (name, amount)
   ACCESSPATTERN (name)
   PRIMARY KEY ...

CREATE TRIGGER ON CustomerOrders INSTEAD OF INSERT AS
   FOR EACH ROW
   BEGIN ATOMIC
   INSERT INTO Customer (...) VALUES (NEW.value ...);
   END

Create Procedure/Function

Using the below syntax, user can define a

• Source Procedure ("CREATE FOREIGN PROCEDURE") - a stored procedure in source

• Source Function ("CREATE FOREIGN FUNCTION") - A function that is supported by the source, where Teiid will pushdown to source instead of evaluating in Teiid engine

• Virtual Procedure ("CREATE VIRTUAL PROCEDURE") - Similar to stored procedure, however this is defined using the Teiid's Procedure language and evaluated in the Teiid's engine.

• Function/UDF ("CREATE VIRTUAL FUNCTION") - A user defined function, where user can define the calling semantics using below, however the function implementation is defined using a JAVA Class.1

Variable Argument Support
Instead of using just an IN parameter, the last non result parameter can be declared VARIADIC to indicate that it can be repeated 0 or more times.

CREATE FOREIGN PROCEDURE proc (x integer, VARIADIC z integer) returns (x string);

FUNCTION OPTIONS:(the below are well known options, any others properties defined will be considered as extension metadata)

Note that NULL-ON-NULL, VARARGS, and all of the AGGREGATE properties are also valid relational extension metadata properties that can be used on source procedures marked as functions. See also Source Supported Functions for creating FOREIGN functions that are supported by a source.

AGGREGATE FUNCTION OPTIONS:

PROCEDURE OPTIONS:(the below are well known options, any others properties defined will be considered as extension metadata)

CREATE VIRTUAL PROCEDURE CustomerActivity(customerid integer) RETURNS (name varchar(25), activitydate date, amount decimal) AS
   BEGIN
   ...
   END

Options

Options can also be added using the ALTER statement. See ALTER stmt.

ALTER Statement

ALTER statements currently primarily supports adding OPTIONS properties to Tables, Views and Procedures. Using a ALTER statement, you can either add, modify or remove a property.

ALTER FOREIGN TABLE "customer" OPTIONS (ADD CARDINALITY 10000);
ALTER FOREIGN TABLE "customer" ALTER COLUMN "name" OPTIONS(SET UPDATABLE FALSE)

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="northwind" version="1">
    <model name="nw">
        <property name="importer.importKeys" value="true"/>
        <property name="importer.importProcedures" value="true"/>
         <source name="northwind-connector" translator-name="mysql" connection-jndi-name="java:/nw-ds"/>
         <metadata type = "NATIVE,DDL"><![CDATA[
              ALTER FOREIGN TABLE "customer" OPTIONS (ADD CARDINALITY 10000);
              ALTER FOREIGN TABLE "customer" ALTER COLUMN "name" OPTIONS(SET UPDATABLE FALSE);
         ]]>
         </metadata>
    </model>
</vdb>

Data Types

The following are the supported data types in the Teiid.

Namespaces for Extension Metadata

When defining the extension metadata in the case of Custom Translators, the properties on tables/views/procedures/columns can define namespace for the properties such that they will not collide with the Teiid specific properties. The property should be prefixed with alias of the Namespace. Prefixes starting with teiid_ are reserved for use by Teiid.

SET NAMESPACE 'http://custom.uri' AS foo

CREATE VIEW MyView (...) OPTIONS ("foo:mycustom-prop" 'anyvalue')